package main

import (
	"fgo/advance/db/gorm/models"
	"github.com/google/martian/log"
)

func main() {
	log.SetLevel(log.Debug)
	db := models.NewDb()

	defer db.Close()

	// 自动迁移模式
	db.AutoMigrate(&models.User{})
	var total int
	if err := db.Model(&models.User{}).Count(&total).Error; err != nil { // SELECT count(*) FROM `user`
		log.Errorf("error: (%v)", err)
	}
	if total <= 0 {
		for i := 0; i < 20; i++ {
			db.Create(&models.User{Name: "abc", Age: int64(i) + 5})
		}
	}

	////////////////// row
	var name string
	var age int
	row := db.Table("user").Where("name = ?", "abc").Select("name, age").Row() // (*sql.Row)
	// SELECT name, age FROM `user`  WHERE (name = 'abc')
	if err := row.Scan(&name, &age); err != nil {
		log.Errorf("error: (%v)", err)
	}
	log.Infof("row scan ==>:  name:(%v),age:(%v)", name, age)

	////////////////// 聚合操作, row
	var sumAge int
	row2 := db.Table("user").Where("name = ?", "abc").Select("SUM(age) as sumAge").Row()
	// SELECT SUM(age) as sumAge FROM `user`
	if err := row2.Scan(&sumAge); err != nil {
		log.Errorf("error: (%v)", err)
	}
	log.Infof("row2 scan ==>:  count:(%v)", sumAge)

	row3 := db.Raw("SELECT SUM(age) as sumAge FROM `user`  WHERE (name = 'abc')").Row()
	var sumAge2 int
	if err := row3.Scan(&sumAge2); err != nil {
		// 数据不存在: ERROR: error: (sql: Scan error on column index 0, name "sumAge": converting driver.Value type <nil> ("<nil>") to a int: invalid syntax)
		log.Errorf("error: (%v)", err)
	} else {

	}
	log.Infof("row3 scan ==>: count:(%v)", sumAge)

	/////// row 处理 float 类型
	row4 := db.Raw("SELECT SUM(score) as sumScore FROM `user`  WHERE (name = 'abc')").Row()
	var sumScore float64
	if err := row4.Scan(&sumScore); err != nil {
		log.Errorf("error: (%v)", err)
	}
	log.Infof("row4 scan ==>: sum:(%v)", sumScore)

	////////////////// rows
	rows, err := db.Model(&models.User{}).Where("name = ?", "abc").Select("name, age").Rows() // (*sql.Rows, error)
	// SELECT name, age FROM `user`  WHERE (name = 'abc')
	if err != nil {
		log.Errorf("error: (%v)", err)
	}
	defer rows.Close()
	for rows.Next() {
		// row 赋值
		var (
			_name string
			_age  int
		)
		if err := rows.Scan(&_name, &_age); err != nil {
			log.Errorf("error: (%v)", err)
		}
		log.Infof("rows scan ==>: _name:(%v), _age:(%v)", _name, _age)
		// 迭代中使用 sql.Rows的Scan
		var user models.User
		if err := db.ScanRows(rows, &user); err != nil {
			log.Errorf("error: (%v)", err)
		}
		log.Infof("rows scan ==>: user:(%v)", user)
	}
}
